SELECT user_id,row_number() over(partition by user_id,flag1 order by click_time)  from   //组内排序
    (WITH temp as 
        (SELECT user_id,click_time,
        if((unix_timestamp(click_time)  - unix_timestamp(lag(click_time) OVER(PARTITION BY user_id order by click_time)))/60 > 30,1,0) as flag 
        from user_clicklog) //temp查询的是 根据id分组，当前行的点击时间和上一次点击时间的差值 > 30分钟则标识为1 否则为0
		// temp1统计的是 根据id分组，把第一行的标识值加到当前行，这样就能对跨越30分钟的访问进行分类
        SELECT user_id,click_time,sum(flag) OVER(PARTITION BY user_id ORDER BY click_time ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as flag1 from temp) temp1